package com.sinaapp.kltq.weathermanager;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.sinaapp.kltq.data.CityListItem;
import com.sinaapp.kltq.data.WeatherAlarm;
import com.sinaapp.kltq.data.WeatherData;
import com.sinaapp.kltq.data.WeatherDay;
import com.sinaapp.kltq.data.WeatherEnvironment;
import com.sinaapp.kltq.data.WeatherRealTime;
import com.sinaapp.kltq.data.WeatherZhishu;

import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

/**
 * Created by 丰小兄 on 2015/7/16.
 * Function: Sqlite层
 */
public class WeatherSqlite {
    public WeatherSqlite(Context context) {
        helper = new SQLiteOpenHelper(context, "user", null, 1) {
            @Override
            public void onCreate(SQLiteDatabase db) {
                db.execSQL("create table weather (city varchar, date varchar, cityIndex integer" +
                        ", updateTime varchar, wendu integer, fengli varchar, shidu varchar, fengxiang varchar, sunrise1 varchar, sunset1 varchar, sunrise2 varchar, sunset2 varchar" +
                        ", eaqi integer, epm25 integer, esuggest varchar, equality varchar, eMajorPollutants varchar, eo3 integer, eco integer, epm10 integer, eso2 integer, eno2 integer, etime varchar" +
                        ", acityKey varchar, acityName varchar, aalarmType varchar, aalarmDegree varchar, aalarmText varchar, aalarmDetails varchar, astandard varchar, aimgURL varchar, atime varchar" +
                        ", ydate varchar, yhigh varchar, ylow varchar, ydayType varchar, ydayFx varchar, ydayFl varchar, ynightType varchar, ynightFx varchar, ynightFl varchar" +
                        ", date1 varchar, high1 varchar, low1 varchar, dayType1 varchar, dayFx1 varchar, dayFl1 varchar, nightType1 varchar, nightFx1 varchar, nightFl1 varchar" +
                        ", date2 varchar, high2 varchar, low2 varchar, dayType2 varchar, dayFx2 varchar, dayFl2 varchar, nightType2 varchar, nightFx2 varchar, nightFl2 varchar" +
                        ", date3 varchar, high3 varchar, low3 varchar, dayType3 varchar, dayFx3 varchar, dayFl3 varchar, nightType3 varchar, nightFx3 varchar, nightFl3 varchar" +
                        ", date4 varchar, high4 varchar, low4 varchar, dayType4 varchar, dayFx4 varchar, dayFl4 varchar, nightType4 varchar, nightFx4 varchar, nightFl4 varchar" +
                        ", date5 varchar, high5 varchar, low5 varchar, dayType5 varchar, dayFx5 varchar, dayFl5 varchar, nightType5 varchar, nightFx5 varchar, nightFl5 varchar" +
                        ", zsname1 varchar, zsvalue1 varchar, zsdetail1 varchar" +
                        ", zsname2 varchar, zsvalue2 varchar, zsdetail2 varchar" +
                        ", zsname3 varchar, zsvalue3 varchar, zsdetail3 varchar" +
                        ", zsname4 varchar, zsvalue4 varchar, zsdetail4 varchar" +
                        ", zsname5 varchar, zsvalue5 varchar, zsdetail5 varchar" +
                        ", zsname6 varchar, zsvalue6 varchar, zsdetail6 varchar" +
                        ", zsname7 varchar, zsvalue7 varchar, zsdetail7 varchar" +
                        ", zsname8 varchar, zsvalue8 varchar, zsdetail8 varchar" +
                        ", zsname9 varchar, zsvalue9 varchar, zsdetail9 varchar" +
                        ", zsname10 varchar, zsvalue10 varchar, zsdetail10 varchar" +
                        ", zsname11 varchar, zsvalue11 varchar, zsdetail11 varchar)");
            }

            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            }
        };
    }

    public List<CityListItem> getCityList() {
        List<CityListItem> list = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        db.beginTransaction();
        try {
            Cursor cursor = db.rawQuery("select city, cityIndex from weather", null);
            while (cursor.moveToNext()) {
                CityListItem item = new CityListItem();
                item.city = cursor.getString(0);
                item.cityIndex = cursor.getInt(1);
                list.add(item);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return list;
    }
    public boolean checkCity(String city, Context context) {
        return WeatherXML.checkCity(city, context);
    }
    public boolean addCityIntoListEnd(String city) {
        SQLiteDatabase db = helper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("city", city);
        values.put("cityIndex", getCityList().size());
        db.insert("weather", null, values);
        return true;
    }
    public boolean delCityFromList(String city) {
        SQLiteDatabase db = helper.getReadableDatabase();
        // delete target city
        db.beginTransaction();
        try {
            Cursor cursor = db.rawQuery("select cityIndex from weather where city='" + city + "'", null);
            cursor.moveToFirst();
            int curIndex = cursor.getInt(0);
            if (curIndex == getCityList().size() - 1) {
                // delete tuple
                db.execSQL("delete from weather where city='" + city + "'");
            } else {
                // delete tuple
                db.execSQL("delete from weather where city='" + city + "'");
                moveIndex(curIndex + 1, getCityList().size(), -1);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return true;
    }

    public boolean dragCityUpon(String city1, String city2) {
        SQLiteDatabase db = helper.getReadableDatabase();
        db.beginTransaction();
        try {
            // get index
            Cursor cursor = db.rawQuery("select cityIndex from weather where city='" + city1 + "'", null);
            cursor.moveToFirst();
            int cityIndex1 = cursor.getInt(0);
            cursor = db.rawQuery("select cityIndex from weather where city='" + city2 + "'", null);
            cursor.moveToFirst();
            int cityIndex2 = cursor.getInt(0);
            // check order
            boolean upward = true;
            if (cityIndex1 <= cityIndex2) {
                upward = false;
            }
            if (upward) {
                // move index between cityIndex2 to cityIndex1 - 1
                moveIndex(cityIndex2, cityIndex1 - 1, 1);
            } else {
                // move index between cityIndex1 - 1 to cityIndex2
                moveIndex(cityIndex1 - 1, cityIndex2, -1);
            }
            // update index of city1 to city2
            db.execSQL("update weather set cityIndex=" + cityIndex2 + " where city='" + city1 + "'");
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return true;
    }
    public boolean updateAllCity(Context context) { // unsafe, not transaction
        // get city list
        List<CityListItem> cityList = WeatherSqlite.this.getCityList();
        // update each of list
        boolean result = true;
        for (CityListItem item : cityList) {
            result = updateCity(item.city, context) ? result : false;
        }
        return result;
    }
    public boolean updateCity(String city, Context context) {
        // check network status
        if (!WeatherXML.checkNetwork(context)) {
            return false;
        }
        // get new weather data of target city
        URL url;
        try {
            url = new URL("http://wthrcdn.etouch.cn/WeatherApi?city=" + URLEncoder.encode(city, "utf-8"));
            WeatherData data = WeatherXML.getWeatherDataByHttp(url, 3000);
            if (data == null) return false;
            SQLiteDatabase db = helper.getReadableDatabase();
            db.beginTransaction();
            try {
                // get city index
                Cursor cursor = db.rawQuery("select cityIndex from weather where city='" + city + "'", null);
                cursor.moveToFirst();
                int cityIndex = cursor.getInt(0);
                // delete city
                db.execSQL("delete from weather where city='" + city + "'");
                // insert new city
                ContentValues values = new ContentValues();
                // insert city data
                values.put("city", data.realTime.city);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                values.put("date", sdf.format(data.date));
                values.put("cityIndex", cityIndex);
                // insert realtime data
                values.put("updateTime", data.realTime.updateTime);
                values.put("wendu", data.realTime.wendu);
                values.put("fengli", data.realTime.fengli);
                values.put("shidu", data.realTime.shidu);
                values.put("fengxiang", data.realTime.fengxiang);
                values.put("sunrise1", data.realTime.sunrise_1);
                values.put("sunset1", data.realTime.sunset_1);
                values.put("sunrise2", data.realTime.sunrise_2);
                values.put("sunset2", data.realTime.sunset_2);
                // insert environment data
                values.put("eaqi", data.environment.aqi);
                values.put("epm25", data.environment.pm25);
                values.put("esuggest", data.environment.suggest);
                values.put("equality", data.environment.quality);
                values.put("eMajorPollutants", data.environment.MajorPollutants);
                values.put("eo3", data.environment.o3);
                values.put("eco", data.environment.co);
                values.put("epm10", data.environment.pm10);
                values.put("eso2", data.environment.so2);
                values.put("eno2", data.environment.no2);
                values.put("etime", data.environment.time);
                // insert alarm data
                values.put("acityKey", data.alarm.cityKey);
                values.put("acityName", data.alarm.cityName);
                values.put("aalarmType", data.alarm.alarmType);
                values.put("aalarmDegree", data.alarm.alarmDegree);
                values.put("aalarmText", data.alarm.alarmText);
                values.put("aalarmDetails", data.alarm.alarmDetails);
                values.put("astandard", data.alarm.standard);
                values.put("aimgURL", data.alarm.imgURL);
                values.put("atime", data.alarm.time);
                // insert yesterday data
                values.put("ydate", data.yesterday.date);
                values.put("yhigh", data.yesterday.high);
                values.put("ylow", data.yesterday.low);
                values.put("ydayType", data.yesterday.dayType);
                values.put("ydayFx", data.yesterday.dayFx);
                values.put("ydayFl", data.yesterday.dayFl);
                values.put("ynightType", data.yesterday.nightType);
                values.put("ynightFx", data.yesterday.nightFx);
                values.put("ynightFl", data.yesterday.nightFl);
                // insert forecast data
                for (int i = 1; i < 6; i ++) {
                    values.put("date" + i, data.forecast.get(i - 1).date);
                    values.put("high" + i, data.forecast.get(i - 1).high);
                    values.put("low" + i, data.forecast.get(i - 1).low);
                    values.put("dayType" + i, data.forecast.get(i - 1).dayType);
                    values.put("dayFx" + i, data.forecast.get(i - 1).dayFx);
                    values.put("dayFl" + i, data.forecast.get(i - 1).dayFl);
                    values.put("nightType" + i, data.forecast.get(i - 1).nightType);
                    values.put("nightFx" + i, data.forecast.get(i - 1).nightFx);
                    values.put("nightFl" + i, data.forecast.get(i - 1).nightFl);
                }
                // insert zhishu data
                for (int i = 1; i < 12; i ++) {
                    values.put("zsname" + i, data.zhishus.get(i - 1).name);
                    values.put("zsvalue" + i, data.zhishus.get(i - 1).value);
                    values.put("zsdetail" + i, data.zhishus.get(i - 1).detail);
                }
                db.insert("weather", null, values);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
            return true;
        } catch (MalformedURLException e) {
            e.printStackTrace();
            return false;
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            return false;
        }
    }
    public WeatherRealTime getRealTimeWeather(String city) {
        WeatherRealTime data = new WeatherRealTime();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select city, updateTime, wendu, fengli, shidu, fengxiang, sunrise1, sunset1, sunrise2, sunset2 from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            data.city           = cursor.getString(0);
            data.updateTime     = cursor.getString(1);
            if (cursor.getString(2) != null) {
                data.wendu = Integer.parseInt(cursor.getString(2));
            }
            data.fengli         = cursor.getString(3);
            data.shidu          = cursor.getString(4);
            data.fengxiang      = cursor.getString(5);
            data.sunrise_1      = cursor.getString(6);
            data.sunset_1       = cursor.getString(7);
            data.sunrise_2      = cursor.getString(8);
            data.sunset_2       = cursor.getString(8);
            
            return data;
        }
        
        return null;
    }
    public List<WeatherDay> getForecastWeather(String city) {
        List<WeatherDay> data = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select ydate, yhigh, ylow, ydayType, ydayFx, ydayFl, ynightType, ynightFx, ynightFl" +
                ", date1, high1, low1, dayType1, dayFx1, dayFl1, nightType1, nightFx1, nightFl1" +
                ", date2, high2, low2, dayType2, dayFx2, dayFl2, nightType2, nightFx2, nightFl2" +
                ", date3, high3, low3, dayType3, dayFx3, dayFl3, nightType3, nightFx3, nightFl3" +
                ", date4, high4, low4, dayType4, dayFx4, dayFl4, nightType4, nightFx4, nightFl4" +
                ", date5, high5, low5, dayType5, dayFx5, dayFl5, nightType5, nightFx5, nightFl5 from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            for (int i = 0; i < 6; i ++) {
                WeatherDay day = new WeatherDay();
                day.date        = cursor.getString(i * 9 + 0);
                day.high        = cursor.getString(i * 9 + 1);
                day.low         = cursor.getString(i * 9 + 2);
                day.dayType     = cursor.getString(i * 9 + 3);
                day.dayFx       = cursor.getString(i * 9 + 4);
                day.dayFl       = cursor.getString(i * 9 + 5);
                day.nightType   = cursor.getString(i * 9 + 6);
                day.nightFx     = cursor.getString(i * 9 + 7);
                day.nightFl     = cursor.getString(i * 9 + 8);
                data.add(day);
            }

            return data;
        }
        
        return null;
    }
    public List<WeatherZhishu> getZhishuWeather(String city) {
        List<WeatherZhishu> data = new ArrayList<>();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select zsname1, zsvalue1, zsdetail1" +
                ", zsname2, zsvalue2, zsdetail2" +
                ", zsname3, zsvalue3, zsdetail3" +
                ", zsname4, zsvalue4, zsdetail4" +
                ", zsname5, zsvalue5, zsdetail5" +
                ", zsname6, zsvalue6, zsdetail6" +
                ", zsname7, zsvalue7, zsdetail7" +
                ", zsname8, zsvalue8, zsdetail8" +
                ", zsname9, zsvalue9, zsdetail9" +
                ", zsname10, zsvalue10, zsdetail10" +
                ", zsname11, zsvalue11, zsdetail11 from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            for (int i = 0; i < 11; i ++) {
                WeatherZhishu zhishu = new WeatherZhishu();
                zhishu.name     = cursor.getString(i * 3 + 0);
                zhishu.value    = cursor.getString(i * 3 + 1);
                zhishu.detail   = cursor.getString(i * 3 + 2);
                data.add(zhishu);
            }
            
            return data;
        }
        
        return null;
    }
    public WeatherEnvironment getEnvironmentWeather(String city) {
        WeatherEnvironment data = new WeatherEnvironment();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select eaqi , epm25 , esuggest , equality , eMajorPollutants , eo3 , eco , epm10 , eso2 , eno2 , etime from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            data.aqi                = cursor.getInt(0);
            data.pm25               = cursor.getInt(1);
            data.suggest            = cursor.getString(2);
            data.quality            = cursor.getString(3);
            data.MajorPollutants    = cursor.getString(4);
            data.o3                 = cursor.getInt(5);
            data.co                 = cursor.getInt(6);
            data.pm10               = cursor.getInt(7);
            data.so2                = cursor.getInt(8);
            data.no2                = cursor.getInt(9);
            data.time               = cursor.getString(10);

            return data;
        }

        return null;
    }
    public WeatherAlarm getAlarmWeather(String city) {
        WeatherAlarm data = new WeatherAlarm();
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select acityKey, acityName, aalarmType, aalarmDegree, aalarmText, aalarmDetails, astandard, aimgURL, atime from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            data.cityKey        = cursor.getString(0);
            data.cityName       = cursor.getString(1);
            data.alarmType      = cursor.getString(2);
            data.alarmDegree    = cursor.getString(3);
            data.alarmText      = cursor.getString(4);
            data.alarmDetails   = cursor.getString(5);
            data.standard       = cursor.getString(6);
            data.imgURL         = cursor.getString(7);
            data.time           = cursor.getString(8);
            
            return data;
        }
        
        return null;
    }
    public Date getUpdateDate(String city) {
        Date date;
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select date from weather where city='" + city + "'", null);
        if (cursor.moveToFirst()) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            try {
                if (cursor.getString(0) != null) {
                    date = sdf.parse(cursor.getString(0));
                    return date;
                }
                return null;
            } catch (ParseException e) {
                e.printStackTrace();
                
                return null;
            }
        }
        
        return null;
    }

    private SQLiteOpenHelper helper;

    private boolean moveIndex(int startIndex, int endIndex, int offset) {
        SQLiteDatabase db = helper.getReadableDatabase();
        db.beginTransaction();
        try {
            Cursor cursor = db.rawQuery("select city, cityIndex from weather", null);
            List<CityListItem> moveList = new ArrayList<>();
            while (cursor.moveToNext()) {
                CityListItem item = new CityListItem();
                item.city = cursor.getString(0);
                item.cityIndex = cursor.getInt(1);
                if (item.cityIndex >= startIndex && item.cityIndex <= endIndex) {
                    moveList.add(item);
                }
            }
            for (CityListItem item : moveList) {
                db.execSQL("update weather set cityIndex=" + (item.cityIndex + offset) + " where city='" + item.city + "'");
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        
        return true;
    }
//    private int getNextIndex() {
//        int nextIndex = getMaxIndex();
//        return ++ nextIndex;
//    }
//    private int getMaxIndex() {
//        SQLiteDatabase db = helper.getReadableDatabase();
//        Cursor cursor = db.rawQuery("select cityIndex from weather", null);
//        int maxIndex = -1;
//        while (cursor.moveToNext()) {
//            int curIndex = Integer.parseInt(cursor.getString(0));
//            maxIndex = (maxIndex > curIndex) ? maxIndex : curIndex;
//        }
//        return maxIndex;
//    }

}
